package com.douqu.game.fileManager;

import com.bean.core.util.EncryptUtils;
import com.bean.core.util.FileUtils;
import com.douqu.game.MenuManager;
import com.douqu.game.core.factory.ConstantFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;


public class ExcelUtils {

	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
	public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

	public static final String EMPTY = "";
	public static final String POINT = ".";
	public static final String TABLE = "\t";
	public static final String LINE = "\n";

	public static void main(String[] args) {
		//利用已经创建的Excel工作薄,创建新的可写入的Excel工作薄


		changeExcel("G:\\MallConfig.xlsx", "nmall", "G:\\");

	}


	public static void changeExcel(String excelFilePath, String outFileName, String excelOutPath)
	{
//		MainFrame.println("源文件路径：" + excelFilePath);
//		MainFrame.println("输出路径：" + excelOutPath);
//		MainFrame.println("文件名：" + outFileName);

		Workbook workbook;
		InputStream is = null;
		try{
			is = new FileInputStream(excelFilePath);
			String postfix = getPostfix(excelFilePath);
			if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
				workbook = new HSSFWorkbook(is);
			}else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
				workbook = new XSSFWorkbook(is);
			}else{
				MenuManager.println("Load " + excelFilePath + " not exist!");
				return;
			}

			Sheet sheet;
			Row row;
			Cell cell;
			sheet = workbook.getSheetAt(0);

//			MainFrame.println("开始生成配置文件：" + outFileName);
			StringBuilder buffer = new StringBuilder();
			int rowLen = sheet.getPhysicalNumberOfRows();
			List<Integer> ignoreIndexs = getIgnoreIndex(sheet.getRow(0));
			ignoreIndexs.addAll(getIgnoreIndex(sheet.getRow(1)));
			ignoreIndexs.addAll(getIgnoreIndex(sheet.getRow(2)));

			String value = "";
			boolean isHaveValue = false;
			for (int rowNum = 0; rowNum < rowLen; rowNum++)
			{
				row = sheet.getRow(rowNum);
				if(row != null)
				{
					int cellLen = row.getPhysicalNumberOfCells();
					if(cellLen == 0) {
						buffer.replace(buffer.length() - 1, buffer.length(), "");
						break;
					}
					isHaveValue = false;
					for (int cellNum = 0; cellNum < cellLen; cellNum++)
					{
						cell = row.getCell(cellNum);
						if(cell != null)
						{
//							System.out.println("--->rowNum->" + rowNum + " --->cellNum->" + cellNum + "--->cellType->" + cell.getCellType());
							cell.setCellType(Cell.CELL_TYPE_STRING);
							value = getValue(cell);
//							System.out.println("获取到的值----->" + value);
							if(value == null || value.equals("") || value.isEmpty())
								continue;
							if(checkIgnoreIndex(ignoreIndexs, cellNum))
								continue;

							buffer.append(value);
							isHaveValue = true;
							if(cellNum != cellLen-1)
								buffer.append(TABLE);
						}
					}
					if(buffer.toString().endsWith(TABLE))
					{
						buffer.replace(buffer.length()-1, buffer.length(), "");
					}

					if(rowNum != rowLen-1 && isHaveValue)
					{
//                		System.out.println("rowNum -> " + rowNum + " rowLen -> " + rowLen);
						buffer.append(System.lineSeparator());
					}
				}
			}
			String str = buffer.toString();
			str = str.endsWith(System.lineSeparator()) ? str.substring(0, str.length()-1) : str;
			FileUtils.writeFile(excelOutPath + "/" + outFileName, str);
			MenuManager.println("转换Excel配置文件成功 ---------->" + outFileName);
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			try {
				if(is != null)
					is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	public static void delPath(String path){
		File file = new File(path);
		if(!file .exists()  && !file .isDirectory()){
			//不存在
			file.mkdirs();
			System.out.println("创建文件夹" + file.toString());
			return;
		}
		File childFile = null;
		if(file.isDirectory()){//判断出file是一个目录
			String[] children = file.list();
			for (int i=0; i<children.length; i++) {
				childFile = new File(file,children[i]);
				if(childFile != null)
					childFile.delete();
			}
		}
	}

	public static boolean propertiesConfigCreate(File outPath,String aId,String sId,String logPath,
												 String sqlAddress,String sqlName,String userName,String password){
		File config = new File(outPath + "\\" + "config.properties");
		try{
			if (!config.exists()){
				config.createNewFile();
				configWrite(config,aId,sId,logPath);
			}
		}catch (Exception e){
			e.printStackTrace();
		}

		File application = new File(outPath,"\\" + "application.properties");
		try {
			if (!application.exists()){
				application.createNewFile();
				applicationWrite(application);
			}
		}catch (Exception e){
			e.printStackTrace();
		}

		File product = new File(outPath,"\\" + "application-product.properties");
		try {
			if (!product.exists()){
				product.createNewFile();
				productWrite(product,sId,sqlAddress,sqlName,userName,password);
			}
		}catch (Exception e){
			e.printStackTrace();
		}

		File log4j = new File(outPath,"\\" + "log4j.properties");
		try{
			if (!log4j.exists()){
				log4j.createNewFile();
				log4jWrite(log4j);
			}
		}catch (Exception e){
			e.printStackTrace();
		}

		//platform暂时不生成 因为太多变化
		return true;
	}

	public static void log4jWrite(File log4j){
		StringBuilder sb = new StringBuilder();
		sb.append("log4j.rootLogger=debug, stdout, D, I, E");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.stdout=org.apache.log4j.ConsoleAppender");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.stdout.Threshold = ALL");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.stdout.layout=org.apache.log4j.PatternLayout");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.stdout.layout.ConversionPattern=[%5p] [%d{yyyy-MM-dd HH:mm:ss.SSS}] %m%n");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.D=org.apache.log4j.DailyRollingFileAppender");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.D.File = logs/slg-debug.log");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.D.DatePattern='_'yyyy-MM-dd'.log'");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.D.Append = true");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.D.Threshold = DEBUG");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.D.Encoding=UTF-8");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.D.layout=org.apache.log4j.PatternLayout");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.D.layout.ConversionPattern=[%5p] [%d{yyyy-MM-dd HH:mm:ss.SSS}] %m%n");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.I=org.apache.log4j.DailyRollingFileAppender");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.I.File = logs/slg-info.log");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.I.DatePattern='_'yyyy-MM-dd'.log'");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.I.Append = true");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.I.Threshold = INFO");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.I.Encoding=UTF-8");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.I.layout=org.apache.log4j.PatternLayout");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.I.layout.ConversionPattern=[%5p] [%d{yyyy-MM-dd HH:mm:ss.SSS}] %m%n");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.E=org.apache.log4j.DailyRollingFileAppender");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.E.File = logs/slg-error.log");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.E.DatePattern='_'yyyy-MM-dd'.log'");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.E.Append = true");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.E.Threshold = ERROR");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.E.Encoding=UTF-8");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.E.layout=org.apache.log4j.PatternLayout");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.appender.E.layout.ConversionPattern=[%5p] [%d{yyyy-MM-dd HH:mm:ss.SSS}] %m%n");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.logger.dl=DUBUG");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.logger.org.springframework=WARN");
		sb.append(ConstantFactory.LINE);
		sb.append("log4j.logger.org.mybatis=info");
		writeFileContent(log4j, sb.toString());
	}

	public static void productWrite(File file,String sid,String sqlAddress,String sqlName,String userName,String password){
		StringBuilder sb = new StringBuilder();
		sb.append("profile=");
		sb.append("product");
		sb.append(ConstantFactory.LINE);
		sb.append("server.port=");
		sb.append(sid);
		sb.append(ConstantFactory.LINE);
		sb.append("server.maxHttpHeaderSize=8192");
		sb.append(ConstantFactory.LINE);
		sb.append("server.tomcat.maxHttpHeaderSize=8192");
		sb.append(ConstantFactory.LINE);
		sb.append("debug=false");
		sb.append(ConstantFactory.LINE);
		sb.append("logging.level.com.douqu.game.main=info");
		sb.append(ConstantFactory.LINE);
		sb.append("logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger %line - %msg%n");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.name = mysql");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.url=jdbc:mysql://");
		sb.append(sqlAddress);
		sb.append(":3306/");
		sb.append(sqlName);
		sb.append("?useUnicode=true&characterEncoding=utf-8&autoReconnect=true");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.username=");
		sb.append(userName);
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.password=");
		sb.append(password);
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.type=com.alibaba.druid.pool.DruidDataSource");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.driver-class-name=com.mysql.jdbc.Driver");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.filters=stat,wall");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.max-active=20");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.initial-size=10");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.max-idle=60000");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.min-idle=1");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.time-between-eviction-runs-millis=60000");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.min-evictable-idle-time-millis=300000");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.validation-query=select 'x'");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.test-while-idle=true");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.test-on-borrow=false");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.test-on-return=false");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.pool-prepared-statements=true");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.max-open-prepared-statements=20");
		sb.append(ConstantFactory.LINE);
		sb.append("mybatis.typeAliasesPackage=com.douqu.game.core.database.model");
		sb.append(ConstantFactory.LINE);
		sb.append("mybatis.mapperLocations=classpath*:com/douqu/game/main/database/mapper/write/*.xml");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.url=jdbc:mysql://");
		sb.append(sqlAddress);
		sb.append(":3306/");
		sb.append(sqlName);
		sb.append("?useUnicode=true&characterEncoding=utf-8&autoReconnect=true");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.username=");
		sb.append(userName);
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.password=");
		sb.append(password);
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.type=com.alibaba.druid.pool.DruidDataSource");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.driver-class-name=com.mysql.jdbc.Driver");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.filters=stat,wall");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.max-active=20");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.initial-size=10");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.max-idle=60000");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.min-idle=1");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.time-between-eviction-runs-millis=60000");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.min-evictable-idle-time-millis=300000");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.validation-query=select 'x'");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.test-while-idle=true");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.test-on-borrow=false");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.test-on-return=false");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.pool-prepared-statements=true");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.datasource.read.max-open-prepared-statements = 20");
		sb.append(ConstantFactory.LINE);
		sb.append("executor.corePoolSize = 10");
		sb.append(ConstantFactory.LINE);
		sb.append("executor.maxPoolSize = 1000");
		sb.append(ConstantFactory.LINE);
		sb.append("executor.queueCapacity = 1000");
		sb.append(ConstantFactory.LINE);
		sb.append("executor.keepAliveSeconds = 300");
		sb.append(ConstantFactory.LINE);
		sb.append("context.listener.classes=com.douqu.game.main.listener.ApplicationEventListener");

		writeFileContent(file,sb.toString());
	}

	public static void applicationWrite(File file){
		StringBuilder sb = new StringBuilder();
		sb.append("#spring.profiles.active=dev");
		sb.append(ConstantFactory.LINE);
		sb.append("#spring.profiles.active=test");
		sb.append(ConstantFactory.LINE);
		sb.append("spring.profiles.active=product");
		writeFileContent(file,sb.toString());
	}

	public static void configWrite(File file,String aId,String sId,String logPath){
		StringBuilder sb = new StringBuilder();
		sb.append("LOG_PATH=");
		sb.append(logPath);
		sb.append(ConstantFactory.LINE);//换行

		sb.append("LANGUAGE=Chinese");
		sb.append(ConstantFactory.LINE);
		sb.append("VERSION=2");
		sb.append(ConstantFactory.LINE);
		sb.append("SERVER_PORT_MAIN=");
		sb.append(sId+"1");
		sb.append(ConstantFactory.LINE);
		sb.append("SERVER_PORT_RMI=");
		sb.append(sId+"2");
		sb.append(ConstantFactory.LINE);
		sb.append("SERVER_URL_RMI=rmi://{0}:{1}/Game");
		sb.append(ConstantFactory.LINE);
		sb.append("SERVER_PORT_RMI_HIDE=");
		sb.append(sId+"3");
		sb.append(ConstantFactory.LINE);
		sb.append("SERVER_PORT_BATTLE=");
		sb.append(sId+"4");
		sb.append(ConstantFactory.LINE);
		sb.append("CAMP_ACTIVITY_ID=");
		sb.append(aId);
		sb.append(ConstantFactory.LINE);
		sb.append("SERVER_PORT_GM=");
		sb.append(sId+"5");

		writeFileContent(file,sb.toString());
	}

	public static void writeFileContent(File file,String newStr){
		FileOutputStream fos = null;
		PrintWriter pw = null;
		try {
			fos = new FileOutputStream(file);
			pw = new PrintWriter(fos);
			pw.write(newStr.toCharArray());
			pw.flush();
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			try {
				if (fos != null){
					fos.close();
				}
				if (pw != null){
					pw.close();
				}
			} catch (Exception e){
				e.printStackTrace();
			}
		}
	}

	public static boolean generateConfig(String excelFilePath, String outFileName, String excelOutPath ) throws Exception{
		//清空整个目录下的东西
		Workbook workbook;
		InputStream is = null;
		try{
			is = new FileInputStream(excelFilePath);
			String postfix = getPostfix(excelFilePath);
			if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
				workbook = new HSSFWorkbook(is);
			}else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
				workbook = new XSSFWorkbook(is);
			}else{
				System.out.println("Load " + excelFilePath + " not exist!");
				return false;
			}

			Sheet sheet;
			Row row;
			Cell cell;
			sheet = workbook.getSheetAt(0);

			System.out.println("开始生成配置文件：" + outFileName);
			StringBuilder buffer = new StringBuilder();
			int rowLen = sheet.getPhysicalNumberOfRows();
			List<Integer> ignoreIndexs = getIgnoreIndex(sheet.getRow(0));
			ignoreIndexs.addAll(getIgnoreIndex(sheet.getRow(1)));
			ignoreIndexs.addAll(getIgnoreIndex(sheet.getRow(2)));

			String value = "";
			boolean isHaveValue = false;
			for (int rowNum = 0; rowNum < rowLen; rowNum++)
			{
				row = sheet.getRow(rowNum);
				if(row != null)
				{
					int cellLen = row.getPhysicalNumberOfCells();
					if(cellLen == 0) {
						buffer.replace(buffer.length() - 1, buffer.length(), "");
						break;
					}
					isHaveValue = false;
					for (int cellNum = 0; cellNum < cellLen; cellNum++)
					{
						cell = row.getCell(cellNum);
						if(cell != null)
						{
//							System.out.println("--->rowNum->" + rowNum + " --->cellNum->" + cellNum + "--->cellType->" + cell.getCellType());
							cell.setCellType(Cell.CELL_TYPE_STRING);
							value = getValue(cell);
//							System.out.println("获取到的值----->" + value);
							if(value == null || value.equals("") || value.isEmpty())
								continue;
							if(checkIgnoreIndex(ignoreIndexs, cellNum))
								continue;

							buffer.append(value);
							isHaveValue = true;
							if(cellNum != cellLen-1)
								buffer.append(TABLE);
						}
					}
					if(buffer.toString().endsWith(TABLE))
					{
						buffer.replace(buffer.length()-1, buffer.length(), "");
					}

					if(rowNum != rowLen-1 && isHaveValue)
					{
//                		System.out.println("rowNum -> " + rowNum + " rowLen -> " + rowLen);
						buffer.append(System.lineSeparator());
					}
				}
			}
			String str = buffer.toString();
			str = str.endsWith(System.lineSeparator()) ? str.substring(0, str.length()-1) : str;
			FileUtils.writeFile(excelOutPath + "/" + outFileName, EncryptUtils.encode(str));
			System.out.println("转换Excel配置文件成功 ---------->" + outFileName);
		}catch (Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			throw new Exception("转换-------- 加密 -------- 异常");
		}finally {
			try {
				if(is != null)
					is.close();
			} catch (IOException e) {
				e.printStackTrace();
				System.out.println(e.getMessage());
				throw new Exception("流关闭异常");
			}
		}

		return true;
	}


	private static boolean checkIgnoreIndex(List<Integer> list, int index)
	{
		for(Integer i : list)
		{
			if(i == index)
				return true;
		}
		return false;
	}

	private static List<Integer> getIgnoreIndex(Row row)
	{
		List<Integer> list = new ArrayList<>();

		if(row == null)
			return list;

		int cellLen = row.getPhysicalNumberOfCells();
		Cell cell = null;
		for (int cellNum = 0; cellNum < cellLen; cellNum++)
		{
			cell = row.getCell(cellNum);
			if(cell != null && getValue(cell).equals("ignore"))
				list.add(cellNum);
		}

		for(Integer i : list)
		{
			MenuManager.println("忽略的INDEX：" + i);
		}

		return list;
	}



	/**
	 * get postfix of the path
	 * @param path
	 * @return
	 */
	public static String getPostfix(String path) {
		if (path == null || EMPTY.equals(path.trim())) {
			return EMPTY;
		}
		if (path.contains(POINT)) {
			return path.substring(path.lastIndexOf(POINT) + 1, path.length());
		}
		return EMPTY;
	}


	private static String getValue(Cell cell)
	{
		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			DecimalFormat df = new DecimalFormat("0");
//        	System.out.println("++++++++++++++df.format(cell.getNumericCellValue())--->"+df.format(cell.getNumericCellValue()));
            String str = String.valueOf(cell.getNumericCellValue());
//			System.out.println("----------------->cell.getNumericCellValue()--->" + cell.getNumericCellValue());
//			String str = df.format(cell.getNumericCellValue());

			if(str.endsWith(".0") || str.endsWith(".00"))
				str = str.substring(0, str.indexOf("."));
			if(str.length() > 12)
				return (int)(Math.round(cell.getNumericCellValue())) + "";

			return str;
		} else {
			cell.setCellType(Cell.CELL_TYPE_STRING);
			return cell.getRichStringCellValue().toString();
		}
	}





}
